package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.*;
import com.info.zhiduoduo.repository.entity.crowdsource.Crowdsource;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 众包自然人 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-09-04
 */
public interface CrowdsourceMapper extends BaseMapper<Crowdsource> {

	@Delete(" DELETE cs,csa,cspi,cstri " +
			" from zdd_crowdsource cs " +
			" left JOIN zdd_crowdsource_account csa on csa.crowdsourceid = cs.crowdsourceid " +
			" left JOIN zdd_crowdsource_project_item cspi on cspi.crowdsourceid = cs.crowdsourceid " +
			" left join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id " +
			" where cs.crowdsourceid = #{crowdsourceid}")
	int delCrowdsource(Integer crowdsourceid);

	@Select(" SELECT c.crowdsource_number FROM zdd_crowdsource_account ca " +
			" LEFT JOIN zdd_crowdsource c ON c.crowdsourceid = ca.crowdsourceid " +
			" WHERE ca.bindcompid = #{companyId} ORDER BY c.createtime DESC LIMIT 1 ")
	String getNewNumber(int companyId);

	@Select(" SELECT IFNULL( c.crowdsource_number, '' ) AS personNo, IFNULL( ca.realname, '' ) AS NAME, " +
			" IFNULL( ca.idno, '' ) AS idNo, IFNULL( ce.audit_time, '' ) AS auditTime, " +
			" IFNULL( ce.state, 0 ) AS state, IFNULL( ce.msg, '' ) AS msg FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid " +
			" LEFT JOIN zdd_crowdsource_examine ce ON ce.crowdsource_number = c.crowdsource_number " +
			" LEFT JOIN zdd_company_foreign cf ON cf.compid = ca.bindcompid " +
			" WHERE c.crowdsource_number = #{crowdsourceNumber} AND cf.merchant_no = #{merchantNo} " +
			" AND c.regfrom = 4 ")
	QueryExamineDto queryExamineByNumber(String crowdsourceNumber, String merchantNo);

	@Select(" SELECT IFNULL( c.crowdsource_number, '' ) AS personNo, IFNULL( ca.realname, '' ) AS NAME, " +
			" IFNULL( ca.idno, '' ) AS idNo, IFNULL( ce.audit_time, '' ) AS auditTime, " +
			" IFNULL( ce.state, 0 ) AS state, IFNULL( ce.msg, '' ) AS msg FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid " +
			" LEFT JOIN zdd_crowdsource_examine ce ON ce.crowdsource_number = c.crowdsource_number " +
			" LEFT JOIN zdd_company_foreign cf ON cf.compid = ca.bindcompid " +
			" WHERE ca.idno = #{idno} AND cf.merchant_no = #{merchantNo} AND c.regfrom = 4 ")
	QueryExamineDto queryExamineByIdNo(String idno, String merchantNo);

	@Select(" SELECT c.crowdsourceid, ca.realname AS realName, ca.idno AS idNo, c.phone, " +
			" c.foreign_card_front_url AS idCardFrontUrl, c.foreign_card_back_url AS idCardBackUrl, " +
			" c.foreign_video_url AS videoUrl, ca.acctno AS acctNo, ca.bankname AS bankName, " +
			" ca.open_acct_bank_name AS bankBranchName FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid " +
			" LEFT JOIN zdd_crowdsource_examine ce ON ce.crowdsource_number = c.crowdsource_number " +
			" WHERE c.foreign_card_front_url IS NOT NULL AND c.foreign_card_back_url IS NOT NULL " +
			" AND c.foreign_video_url IS NOT NULL AND ca.push_flag = 0 AND ce.state = 1 " +
			" ORDER BY ca.createtime DESC LIMIT 30 ")
	List<QueryYunKongUnregisteredDto> queryYunKongUnregistered();

	@Select(" SELECT c.crowdsourceid, ca.realname, c.crowdsource_number, ca.natural_number, ca.bindcompid " +
			" FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON c.crowdsourceid = ca.crowdsourceid " +
			" WHERE c.regfrom = 4 AND ca.realname_auth = 3 AND ca.natural_number IS NOT NULL AND ca.bindcompid = #{companyId} ")
	List<CrowdsourceJiaYinDto> queryJiaYinCrowdsource(int companyId);


	@Select(" SELECT IFNULL( c.crowdsource_number, '' ) AS personNo, IFNULL( ca.realname, '' ) AS NAME, " +
			" IFNULL( ca.idno, '' ) AS idNo, IFNULL( ce.audit_time, '' ) AS auditTime, " +
			" IFNULL( ce.state, 0 ) AS state, IFNULL( ce.msg, '' ) AS msg FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid " +
			" LEFT JOIN zdd_crowdsource_examine ce ON ce.crowdsource_number = c.crowdsource_number " +
			" WHERE c.regfrom = 5 AND ca.bindcompid = #{companyId} " +
			" AND IF(ISNULL(#{crowdsourceNumber}) OR #{crowdsourceNumber} = '', 1=1, c.crowdsource_number = #{crowdsourceNumber}) " +
			" AND IF(ISNULL(#{requestNo}) OR #{requestNo} = '', 1=1, c.request_no = #{requestNo}) " +
			" AND IF(ISNULL(#{idNo}) OR #{idNo} = '', 1=1, ca.idno = #{idNo}) ")
	QueryExamineDto queryExamineByCondition(int companyId, String crowdsourceNumber, String requestNo, String idNo);

	@Select(" SELECT c.crowdsourceid, c.crowdsource_number AS personNo, ca.realname_auth, " +
			" ca.idno, ca.bindcompid AS companyId FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid " +
			" WHERE ca.idno = #{idNo} AND c.regfrom = ${regfrom} ")
	QueryCrowdsourceInfoDto queryCrowdsourceInfoByCondition(String idNo, int regfrom);

	@Select(" SELECT c.crowdsourceid ,ca.realname AS name, ca.idno AS idNo FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid " +
			" LEFT JOIN " +
			"	   (SELECT crowdsourceid, project_name FROM zdd_foreign_project GROUP BY crowdsourceid) " +
			"	   fp ON fp.crowdsourceid = c.crowdsourceid " +
			" WHERE c.regfrom = 4 AND fp.project_name IS NOT NULL " +
			" AND DATE(c.createtime) BETWEEN #{startDate} AND #{endDate} ")
	List<ExportJiaYinNaturalPersonDto> queryJiaYinNaturalPerson(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

	@Select(" SELECT c.crowdsourceid AS userId, @i := @i + 1 AS row, ca.realname AS name, ca.acctno, ca.idno " +
			" FROM zdd_crowdsource c " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid, " +
			" ( SELECT @i := 0 ) r " +
			" WHERE ca.realname_auth = 3 AND ca.bindcompid = #{companyId} ")
	List<ExportProjectTemplateDto> queryCompanyCrowdsource(@Param("companyId") int companyId);

	@Select("SELECT c.phone,ca.idno  " +
			"FROM zdd_crowdsource c " +
			"LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = c.crowdsourceid " +
			"WHERE c.crowdsourceid = #{userId} ")
	GetPhoneDto getPhone(@Param("userId") Integer userId);
}
